"""
Case Type   : FDW
Case Name   : 验证create server提供正确options对外联表进行操作
Create At   : 2024/10/16
Owner       : lonely-dance
Description :
    1.安装mysql_fdw依赖并重启数据库
    2.mysql端建表
    3.创建扩展
    4.创建服务器
    5.创建外部表
    6.查询外部表
    7.清理环境
Expect      :
    1.成功
    2.创建成功
    3.创建成功
    4.创建成功
    5.创建成功
    6.查询成功
    7.成功
History     :
"""

import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.ComMysql import ComMysql
from testcase.utils.Logger import Logger
from yat.test import macro
from yat.test import Node

pri = CommonSH('PrimaryDbUser')
system, arch = pri.get_system_arch()
uname = system + '_' + arch
class GrammarFDW(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'---{os.path.basename(__file__)} start---')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.common = Common('PrimaryDbUser')
        self.p_node = Node('PrimaryDbUser')
        self.gauss_home = self.p_node.sh(f'source {macro.DB_ENV_PATH};echo $GAUSSHOME;').result()
        self.p_root_node = Node('PrimaryRoot')
        self.mysql_node = Node('mysql5')
        self.so_file = os.path.join(macro.SCRIPTS_PATH, f"mysql_fdw/{uname}/lib/")
        self.extension_file_path = os.path.join(macro.SCRIPTS_PATH, f"mysql_fdw/{uname}/extension/")
        self.extension_file_names = ['mysql_fdw--1.0--1.1.sql', 'mysql_fdw--1.0.sql', 'mysql_fdw--1.1.sql', 'mysql_fdw.control']
        self.og_lib_path = os.path.join(f'{self.gauss_home}', "lib/postgresql")
        self.og_extension_path = os.path.join(f'{self.gauss_home}', "share/postgresql/extension")
        self.constant = Constant()
        self.mysql_sh = ComMysql(host=self.mysql_node.db_host, port=int(self.mysql_node.db_port), user=self.mysql_node.db_user,
                                 password=self. mysql_node.db_password, database=self.mysql_node.db_name)
        self.og_database = 'fdw'

    def test_grammar_FDW(self):
        text = '-----step1:安装mysql_fdw依赖并重启数据库 expect：成功-----'
        self.log.info(text)
        res = self.common.scp_file(self.p_node, 'mysql_fdw.so', self.og_lib_path, source_dir=self.so_file)
        self.log.info(res)
        self.assertIsNone(res, '执行失败:' + text)
        for file_name in self.extension_file_names:
            res = self.common.scp_file(self.p_node, file_name, self.og_extension_path, source_dir=self.extension_file_path)
            self.log.info(res)
            self.assertIsNone(res, '执行失败:' + text)

        cmd = f"source {macro.DB_ENV_PATH};" \
            f"gs_ssh -c \"gs_guc generate -o usermapping -S default -D $GAUSSHOME/bin\";"
        res = self.p_node.sh(cmd).result()
        self.log.info(res)
        self.assertIn(self.constant.gs_ssh_success_msg, res, '执行失败:' + text)

        restart_res = self.primary_sh.restart_db_cluster()
        self.assertTrue(restart_res, '执行失败:' + text)

        text = '-----step2:MySQL 端建表 expect：创建成功-----'
        self.log.info(text)
        self.mysql_sh.connect()
        cmd = f"""
            USE {self.mysql_node.db_name};
            DROP TABLE if exists tab_fdw;
            CREATE TABLE tab_fdw (col1 INT primary key, col2 VARCHAR(20));
            INSERT INTO tab_fdw VALUES (1, 'aa'), (2, 'bb'), (3, 'cc'), (4, 'dd');
        """
        self.log.info(cmd)
        res = self.mysql_sh.exec_sql(cmd)
        self.log.info(res)
        self.assertNotIn('ERROR', res, '执行失败:' + text)

        text = '-----step3:创建扩展 expect：创建成功-----'
        self.log.info(text)
        cmd = f"""
                    drop database if exists {self.og_database};
                    create database {self.og_database} with dbcompatibility = 'B' encoding 'utf8';
                """
        res = self.primary_sh.execut_db_sql(cmd)
        self.log.info(res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, res, '执行失败:' + text)

        cmd = "drop extension if exists mysql_fdw;" \
               "create extension mysql_fdw;"
        self.log.info(cmd)
        res = self.primary_sh.execut_db_sql(cmd, dbname=self.og_database)
        self.log.info(res)
        self.assertIn(self.constant.create_extension_success, res, '执行失败:' + text)
        self.assertIn(self.constant.drop_extension_success, res, '执行失败:' + text)

        text = '-----step4:创建服务器 expect：创建成功-----'
        self.log.info(text)
        cmd = f"""
            DROP SERVER IF EXISTS server_fdw_0004 CASCADE;
            CREATE SERVER server_fdw_0004
                FOREIGN DATA WRAPPER mysql 
                OPTIONS (host '{self.mysql_node.db_host}', port '{self.mysql_node.db_port}', 
                user '{self.mysql_node.db_user}', password '{self.mysql_node.db_password}');
        """
        self.log.info(cmd)
        res = self.primary_sh.execut_db_sql(cmd, dbname=self.og_database)
        self.log.info(res)
        self.assertIn(self.constant.drop_server_success, res, '执行失败:' + text)
        self.assertIn(self.constant.create_server_success, res, '执行失败:' + text)

        text = '-----step5:创建外部表 expect：创建成功-----'
        self.log.info(text)
        cmd = f"""
            DROP FOREIGN TABLE IF EXISTS tab_fdw_0004;
            CREATE FOREIGN TABLE tab_fdw_0004 (col1 INT, col2 VARCHAR(20))
                SERVER server_fdw_0004 
                OPTIONS (dbname '{self.mysql_node.db_name}', table_name 'tab_fdw');
        """
        self.log.info(cmd)
        res = self.primary_sh.execut_db_sql(cmd, dbname=self.og_database)
        self.log.info(res)
        self.assertIn(self.constant.DROP_FOREIGN_SUCCESS_MSG, res, '执行失败:' + text)
        self.assertIn(self.constant.CREATE_FOREIGN_SUCCESS_MSG, res, '执行失败:' + text)

        text = '-----step6:查询外部表 expect：查询成功-----'
        self.log.info(text)
        cmd = f"""
            SELECT * FROM tab_fdw_0004;
        """
        self.log.info(cmd)
        res = self.primary_sh.execut_db_sql(cmd, dbname=self.og_database)
        self.log.info(res)
        self.assertIn('(4 rows)', res, '执行失败:' + text)

    def tearDown(self):
        text = '-----step7:清理环境 expect:成功-----'
        self.log.info(text)
        cmd = f'''
            DROP DATABASE IF EXISTS {self.og_database};
        '''
        self.log.info(cmd)
        res = self.primary_sh.execut_db_sql(cmd)
        self.log.info(res)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, res, '执行失败:' + text)

        cmd = f"""
            USE {self.mysql_node.db_name};
            DROP TABLE if exists tab_fdw;
        """
        self.mysql_sh.connect()
        self.log.info(cmd)
        res = self.mysql_sh.exec_sql(cmd)
        self.mysql_sh.close()
        self.log.info(res)
        self.assertNotIn('ERROR', res, '执行失败:' + text)

        cmd = f"rm -rf {os.path.join(self.og_lib_path, 'mysql_fdw.so')};" \
               f"cd {self.og_extension_path} ;" \
               f"rm -rf {self.extension_file_names} ;"
        self.log.info(cmd)
        res = self.primary_sh.exec_gs_ssh(cmd)
        self.log.info(res)
        self.assertNotIn('ERROR', res, '执行失败:' + text)

        self.log.info(f'---{os.path.basename(__file__)} end---')